﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using music.Models;

namespace music.DataAccess
{
    public class Queries
    {
        private MusicDBDataContext musicdbContext;
        private string DBConnection
        {
            get { return ConfigurationManager.ConnectionStrings["MusicStore"].ToString(); }
        }
        public Queries()
        {
            musicdbContext = new MusicDBDataContext(DBConnection);
        }
        public void SubMitChanges()
        {
            musicdbContext.SubmitChanges();
        }
        public void UpdateAlbum(Models.Album album)
        {
            var Entalb = musicdbContext.Albums
                .Where(al => al.AlbumId == album.AlbumId).FirstOrDefault();
            Entalb.AlbumId = album.AlbumId;
            Entalb.ArtistId = album.ArtistId;
            Entalb.GenreId = album.GenreId;
            Entalb.Title = album.Title;
            Entalb.Price = album.Price;
        }
        public Models.Album GetAlbumById(long albumId)
        {
            //var Entalb = musicdbContext.Albums
            //           .Where(al => al.AlbumId == albumId)
            //           .FirstOrDefault();

            //Models.Album modelAlbum = new Models.Album();
            //modelAlbum.AlbumId = Entalb.AlbumId;
            //modelAlbum.ArtistId = Entalb.ArtistId;
            //modelAlbum.GenreId = Entalb.GenreId;
            //modelAlbum.Title = Entalb.Title;
            //modelAlbum.Price = Entalb.Price;
            //return modelAlbum;

            return (from al in musicdbContext.Albums
                    where al.AlbumId == albumId
                    select new Models.Album
                               {
                                   AlbumId = al.AlbumId,
                                   ArtistId = al.ArtistId,
                                   GenreId = al.GenreId,
                                   Title = al.Title,
                                   Price = al.Price,
                               }
                   ).FirstOrDefault();


            //var a = musicdbContext.Albums
            //    .Where(al => al.AlbumId == albumId)
            //    .Select(g => new Models.Album
            //                     {
            //                         AlbumId = g.AlbumId,
            //                         ArtistId = g.ArtistId,
            //                         GenreId = g.GenreId,
            //                         Title = g.Title,
            //                         Price = g.Price,
            //                     }).FirstOrDefault();
            //return a;



            // select * from album al 
            //where al.Albumid = albumId
        }
        public GenreViewModel GetGenreModel()
        {
            GenreViewModel model = new GenreViewModel();
            model.GenreCount = musicdbContext.Genres.Count();
            model.Genres = (from gr in musicdbContext.Genres
                            select new Models.Genre
                                       {
                                           GenreId = gr.GenreId,
                                           Name = gr.Name,
                                           Description = gr.Description
                                       }).ToList();
            return model;
        }

        public AlbumViewModel GetAlbumByGenreName(string genreName)
        {
            AlbumViewModel model = new AlbumViewModel();
            model.Genre = (from ge in musicdbContext.Genres
                           where ge.Name == genreName
                           select new Models.Genre
                                      {
                                          GenreId = ge.GenreId,
                                          Name = ge.Name,
                                          Description = ge.Description
                                      }).FirstOrDefault();
            model.Albums = (from al in musicdbContext.Albums
                            where al.GenreId == model.Genre.GenreId
                            select new Models.Album
                                       {
                                           AlbumId = al.AlbumId,
                                           GenreId = al.GenreId,
                                           ArtistId = al.ArtistId,
                                           Price = al.Price,
                                           Title = al.Title,
                                           AlbumArtUrl = al.AlbumArtUrl
                                       }).ToList();
            return model;
        }
    }
}
